﻿using DBUtil.Builders;
using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Linq.Expressions;

namespace DBUtil;

public partial class DBAccess
{
    #region 方便构建sql
    #region 泛型&非泛型 SchemaPure/SchemaMetaDataSeg
    /// <summary>
    /// 获取定义在实体类上的schema名称, 示例:
    /// <code>
    /// //sqlserver示例
    /// [Table("t_user", Schema ="dbo")]
    /// //or
    /// [Table("dbo.t_user")]
    /// //or
    /// [Table("[remote_link].[testdb].[dbo].[t_user]")]
    /// public class Person {}
    /// 
    /// //获取schema的纯净名称
    /// db.SchemaPure&lt;Person>().ShouldBe("dbo")
    /// 
    /// //获取schema的seg名称, 可用于元数据查询
    /// db.SchemaMetaDataSeg&lt;Person>().ShouldBe("'dbo'")
    /// </code>
    /// </summary>
    public string SchemaPure<TEntity>() where TEntity : class, new()
        => GetEntityInfoInternal(typeof(TEntity), false).TableName.SchemaName;
    /// <inheritdoc cref="SchemaPure{TEntity}"/>
    public string SchemaMetaDataSeg<TEntity>() where TEntity : class, new()
        => ProtectStringToSeg(GetEntityInfoInternal(typeof(TEntity), false).TableName.SchemaName);

    /// <summary>
    /// 获取定义在实体类上的schema名称, 示例:
    /// <code>
    /// //sqlserver示例
    /// [Table("t_user", Schema ="dbo")]
    /// //or
    /// [Table("dbo.t_user")]
    /// //or
    /// [Table("[remote_link].[testdb].[dbo].[t_user]")]
    /// public class Person {}
    /// 
    /// //获取schema的纯净名称
    /// db.SchemaPure(typeof(Person)).ShouldBe("dbo")
    /// 
    /// //获取schema的seg名称, 可用于元数据查询
    /// db.SchemaMetaDataSeg(typeof(Person)).ShouldBe("'dbo'")
    /// </code>
    /// </summary>
    public string SchemaPure(Type type)
        => GetEntityInfoInternal(type, false).TableName.SchemaName;
    /// <inheritdoc cref="SchemaPure(Type)"/>
    public string SchemaMetaDataSeg(Type type)
        => ProtectStringToSeg(GetEntityInfoInternal(type, false).TableName.SchemaName);
    #endregion

    #region 泛型 TablePure/TableMetaDataSeg/TableFullQuoted
    /// <summary>
    /// 获取定义在实体类上的纯净表名称, 示例:
    /// <code>
    /// //sqlserver示例
    /// [Table("t_user", Schema ="dbo")]
    /// //or
    /// [Table("dbo.t_user")]
    /// //or
    /// [Table("[remote_link].[testdb].[dbo].[t_user]")]
    /// public class Person {}
    /// 
    /// //获取表的纯净名称
    /// db.TablePure&lt;Person>().ShouldBe("t_user")
    /// db.TablePure(typeof(Person)).ShouldBe("t_user")
    /// </code>
    /// </summary>
    public string TablePure<TEntity>() where TEntity : class, new()
        => GetEntityInfoInternal(typeof(TEntity), false).TableName.Name;
    /// <summary>
    /// 获取定义在实体类上的表元数据seg名称, 示例:
    /// <code>
    /// //sqlserver示例
    /// [Table("t_user", Schema ="dbo")]
    /// //or
    /// [Table("dbo.t_user")]
    /// //or
    /// [Table("[remote_link].[testdb].[dbo].[t_user]")]
    /// public class Person {}
    /// 
    /// //获取表的seg名称, 可用于元数据查询
    /// db.TableMetaDataSeg&lt;Person>().ShouldBe("'t_user'")
    /// db.TableMetaDataSeg(typeof(Person)).ShouldBe("'t_user'")
    /// </code>
    /// </summary>
    public string TableMetaDataSeg<TEntity>() where TEntity : class, new()
        => ProtectStringToSeg(GetEntityInfoInternal(typeof(TEntity), false).TableName.Name);
    /// <summary>
    /// 获取定义在实体类上的表全引用名称, 示例:
    /// <code>
    /// //sqlserver示例
    /// [Table("t_user", Schema ="dbo")]
    /// //or
    /// [Table("dbo.t_user")]
    /// //or
    /// [Table("[remote_link].[testdb].[dbo].[t_user]")]
    /// public class Person {}
    /// 
    /// //获取表的quote名称, 可用于表数据查询
    /// db.TableFullQuoted&lt;Person>() 
    ///     => 
    ///     .ShouldBe("[dbo].[t_user]")
    ///     or
    ///     .ShouldBe("[remote_link].[testdb].[dbo].[t_user]")
    /// 
    /// //当表有别名时
    /// db.TableFullQuoted&lt;Person>("t") 
    ///     => 
    ///     .ShouldBe("[dbo].[t_user] t")
    ///     or
    ///     .ShouldBe("[remote_link].[testdb].[dbo].[t_user] t")
    /// </code>
    /// </summary>
    public string TableFullQuoted<TEntity>() where TEntity : class, new()
        => GetEntityInfoInternal(typeof(TEntity), false).TableName.FullNameQuoted;
    /// <inheritdoc cref="TableFullQuoted{TEntity}()"/>
    public string TableFullQuoted<TEntity>(string alias) where TEntity : class, new()
    {
        var name = GetEntityInfoInternal(typeof(TEntity), false).TableName.FullNameQuoted;
        if (alias.IsNullOrWhiteSpace()) return name;
        return $"{name} {alias}";
    }
    /// <summary>
    /// 获取定义在实体类上的表全引用名称, 示例:
    /// <code>
    /// //sqlserver示例
    /// [Table("t_user", Schema ="dbo")]
    /// //or
    /// [Table("dbo.t_user")]
    /// //or
    /// [Table("[remote_link].[testdb].[dbo].[t_user]")]
    /// public class Person {}
    /// 
    /// //获取表的quote名称
    /// //当有分表时, 方式一:
    /// db.TableFullQuoted&lt;Person>(pureName => pureName + "_20250101", "t")
    ///     =>
    ///     .ShouldBe("[dbo].[t_user_20250101] t") 
    ///     or
    ///     .ShouldBe("[remote_link].[testdb].[dbo].[t_user_20250101] t")
    ///
    /// //当有分表时, 方式二:
    /// db.TableFullQuoted&lt;Person>(obj => obj.SetName(obj.Name + "_20250101"), "t")
    ///     =>
    ///     .ShouldBe("[dbo].[t_user_20250101] t") 
    ///     or
    ///     .ShouldBe("[remote_link].[testdb].[dbo].[t_user_20250101] t")
    /// </code>
    /// </summary>
    public string TableFullQuoted<TEntity>(Func<string, string> getNewTableNameFunc, string alias = null) where TEntity : class, new()
    {
        if (getNewTableNameFunc == null) return TableFullQuoted<TEntity>(alias);
        var objName = GetEntityInfoInternal(typeof(TEntity), getNewTableNameFunc != null).TableName;
        objName.SetName(getNewTableNameFunc(objName.Name));
        if (alias.IsNullOrWhiteSpace()) return objName.FullNameQuoted;
        return $"{objName.FullNameQuoted} {alias}";
    }
    /// <inheritdoc cref="TableFullQuoted{TEntity}(Func{string, string}, string)"/>
    public string TableFullQuoted<TEntity>(Action<TableName> setTableNameAct, string alias = null) where TEntity : class, new()
    {
        if (setTableNameAct == null) return TableFullQuoted<TEntity>(alias);
        var objName = GetEntityInfoInternal(typeof(TEntity), setTableNameAct != null).TableName;
        setTableNameAct(objName);
        if (alias.IsNullOrWhiteSpace()) return objName.FullNameQuoted;
        return $"{objName.FullNameQuoted} {alias}";
    }
    #endregion

    #region 非泛型 TablePure/TableMetaDataSeg/TableFullQuoted
    /// <inheritdoc cref="TablePure{TEntity}()"/>
    public string TablePure(Type type)
        => GetEntityInfoInternal(type, false).TableName.Name;
    /// <inheritdoc cref="TableMetaDataSeg{TEntity}()" />
    public string TableMetaDataSeg(Type type)
        => ProtectStringToSeg(GetEntityInfoInternal(type, false).TableName.Name);
    /// <inheritdoc cref="TableFullQuoted{TEntity}()"/>
    public string TableFullQuoted(Type type)
        => GetEntityInfoInternal(type, false).TableName.FullNameQuoted;
    /// <inheritdoc cref="TableFullQuoted{TEntity}()"/>
    public string TableFullQuoted(Type type, string alias)
    {
        var name = GetEntityInfoInternal(type, false).TableName.FullNameQuoted;
        if (alias.IsNullOrWhiteSpace()) return name;
        return $"{name} {alias}";
    }
    /// <inheritdoc cref="TableFullQuoted{TEntity}(Func{string, string}, string)"/>
    public string TableFullQuoted(Type type, Func<string, string> getNewTableNameFunc, string alias = null)
    {
        if (getNewTableNameFunc == null) return TableFullQuoted(type, alias);
        var objName = GetEntityInfoInternal(type, getNewTableNameFunc != null).TableName;
        objName.SetName(getNewTableNameFunc(objName.Name));
        if (alias.IsNullOrWhiteSpace()) return objName.FullNameQuoted;
        return $"{objName.FullNameQuoted} {alias}";
    }
    /// <inheritdoc cref="TableFullQuoted{TEntity}(Func{string, string}, string)"/>
    public string TableFullQuoted(Type type, Action<TableName> setTableNameAct, string alias = null)
    {
        if (setTableNameAct == null) return TableFullQuoted(type, alias);
        var objName = GetEntityInfoInternal(type, setTableNameAct != null).TableName;
        setTableNameAct(objName);
        if (alias.IsNullOrWhiteSpace()) return objName.FullNameQuoted;
        return $"{objName.FullNameQuoted} {alias}";
    }
    #endregion

    #region ColumnPure
    /// <summary>
    /// 获取定义的实体类的属性上的纯净列名称, 如:
    /// <code>
    /// [Table("t_person")]
    /// public class Person
    /// {
    ///     [PrimaryKey, Identity]
    ///     public int Id { get; set; }
    ///     
    ///     [Column("old_name"]
    ///     public string OldName { get; set; }
    /// }
    /// 
    /// db.ColumnPure(typeof(Person), nameof(Person.OldName)).ShouldBe("old_name")
    /// db.ColumnPure&lt;Person>(i => i.OldName).ShouldBe("old_name")
    /// db.ColumnPure&lt;Person>(i => i.Id).ShouldBe("Id")
    /// </code>
    /// </summary>
    public string ColumnPure(Type type, string propName)
    {
        AssertUtil.NotNull(type);
        AssertUtil.NotNullOrWhiteSpace(propName);
        var ent = GetEntityInfoInternal(type, false);
        return ent.Props.FirstOrDefault(i => i.PropNamePure == propName)?.ColumnNamePure;
    }
    /// <inheritdoc cref="ColumnPure(Type, string)"/>
    public string ColumnPure<TEntity>(Expression<Func<TEntity, object>> expr) where TEntity : class, new()
    {
        AssertUtil.NotNull(expr);
        var props = ExpressionHelper.GetAccessNames(expr);
        AssertUtil.NotNullOrEmpty(props, $"传入的表达式({expr})错误, 正确示例: i => i.Name");
        return ColumnPure(typeof(TEntity), props[0]);
    }
    #endregion

    #region ColumnMetaDataSeg
    /// <summary>
    /// 获取列名seg格式(可用于元数据查询), 示例:
    /// <code>
    /// public class Person
    /// {
    ///     [Column("k_id")
    ///     public int Id { get; set; }
    /// }
    /// 
    /// db.ColumnMetaDataSeg(typeof(Person), "Id").ShouldBe("'k_id'")
    /// db.ColumnMetaDataSeg&lt;Person>(i => i.Id).ShouldBe("'k_id'")
    /// </code>
    /// </summary>
    public string ColumnMetaDataSeg(Type type, string propName)
    {
        AssertUtil.NotNull(type);
        AssertUtil.NotNullOrWhiteSpace(propName);
        var entityInfo = GetEntityInfoInternal(type, false);
        return ProtectStringToSeg(entityInfo.Props.Where(i => i.PropNamePure == propName).FirstOrDefault()?.ColumnNamePure);
    }
    /// <inheritdoc cref="ColumnMetaDataSeg(Type, string)"/>
    public string ColumnMetaDataSeg<TEntity>(Expression<Func<TEntity, object>> expr) where TEntity : class, new()
    {
        AssertUtil.NotNull(expr);
        var props = ExpressionHelper.GetAccessNames(expr);
        AssertUtil.NotNullOrEmpty(props, $"传入的表达式({expr})错误, 正确示例: i=>i.Name");
        return ColumnMetaDataSeg(typeof(TEntity), props[0]);
    }
    #endregion

    #region ColumnQuoted
    /// <summary>
    /// 获取列名quoted格式(可用于拼接sql询), 示例:
    /// <code>
    /// public class Person
    /// {
    ///     [Column("k_id")
    ///     public int Id { get; set; }
    /// }
    /// 
    /// db.ColumnQuoted(typeof(Person), "Id").ShouldBe("`k_id`")
    /// db.ColumnQuoted&lt;Person>(i => i.Id).ShouldBe("`k_id`")
    /// </code>
    /// </summary>
    public string ColumnQuoted(Type type, string propName)
    {
        AssertUtil.NotNull(type);
        AssertUtil.NotNullOrWhiteSpace(propName);
        var entityInfo = GetEntityInfoInternal(type, false);
        return entityInfo.Props.FirstOrDefault(i => i.PropNamePure == propName)?.ColumnNameQuoted;
    }
    /// <inheritdoc cref="ColumnQuoted(Type, string)"/>
    public string ColumnQuoted<TEntity>(Expression<Func<TEntity, object>> expr) where TEntity : class, new()
    {
        AssertUtil.NotNull(expr);
        var props = ExpressionHelper.GetAccessNames(expr);
        AssertUtil.NotNullOrEmpty(props, $"传入的表达式({expr})错误, 正确示例: i=>i.Name");
        return ColumnQuoted(typeof(TEntity), props[0]);
    }
    #endregion

    #region ParseToSql
    internal string ParseToSqlInternal(LambdaExpression expression, string[] prefixes, bool[] enum2Strings)
    {
        AssertUtil.NotNull(expression);

        var (exp, midValues) = ExpressionHelper.ReduceLambda(expression, isKeepCallBack: BuilderHelper.IsKeepCallBackForSubSelect);
        var res = BuilderHelper.GetConstant(exp, midValues);
        if (res.Success) return ConvertToSqlSeg(res.Data).Data;

        var lambda = exp as LambdaExpression;
        var aliases = new List<KeyValuePair<ParameterExpression, string>>();
        var parameters = new List<ParameterExpression>();
        var enum2StringsMap = new Dictionary<ParameterExpression, bool>();
        var idx = 0;
        foreach (var para in lambda.Parameters)
        {
            var hasPrefix = (prefixes?.Length ?? 0) > idx && prefixes[idx].IsNotNullOrWhiteSpace();

            //尝试将参数识别为列, 而不是表(如果是列, 需要加 quote 符号; 如果是表, 则不需要)
            //如果定义了前缀, 则参数肯定表示列
            //如: db.ParseToSql<int>(age => age > 18,"t")
            //输出: t.`age`>18
            var isColumDirectly = hasPrefix;
            //如果参数是一个简单类型, 则肯定表示列
            //如: db.ParseToSql<int>(age => age > 18)
            //输出: `age`>18
            if (!isColumDirectly) isColumDirectly = para.Type.IsSimpleType();
            //如果参数用的是系统类, 则肯定表示列
            //如: db.ParseToSql<Dictionary<string,string>>(ext => ext["name"]=="tom")
            //输出: json_value(ext,'$.\"name\"')='tom'
            //或者是 db.ParseToSql<JsonObject>(),db.ParseToSql<JsonArray>()
            if (!isColumDirectly) isColumDirectly = para.Type.Namespace.StartsWith("System.");

            if (hasPrefix)
            {
                //指定了前缀(一般是表别名)
                aliases.Add(new KeyValuePair<ParameterExpression, string>(para, prefixes[idx] + "." + AddQuote(para.Name)));
            }
            else
            {
                //没有指定前缀(可能是表别名也有可能是列名)
                var alias = para.Name;
                if (isColumDirectly) alias = AddQuote(para.Name);
                else alias = para.Name.StartsWith('_') ? null : para.Name;
                aliases.Add(new KeyValuePair<ParameterExpression, string>(para, alias));
            }
            if (idx < (enum2Strings?.Length ?? 0)) enum2StringsMap.Add(para, enum2Strings[idx]);
            parameters.Add(para);
            idx++;
        }
        return BuilderHelper.ParseSql(expression: lambda, db: this, aliasesMap: aliases, parameters: parameters, midValues: midValues, enum2StringMap: enum2StringsMap);
    }

    /// <summary>
    /// 解析sql, 示例(mysql):
    /// <code>
    /// //用于select子句
    /// //只要参数名以 '_' 开头则省略表别名
    /// db.ParseToSql&lt;Person>(_t => _t.Id).ShouldBe("`id`");
    /// 
    /// //下面 Ext.Score 是json类型 如: [JsonMap]public ExtInfo Ext { get; set; } public class ExtInfo{ public double Score {get;set;} }
    /// db.ParseToSql&lt;Person>(t => new { t.Id, Remark = t.Age > 18 ? "成年" : "未成年", t.Ext.Score }).ShouldBe("t.`id` `Id`,if((t.`age` > 18),'成年','未成年') `Remark`,json_value(t.`Ext`,'$.\"Score\"' returning double) `Score`");
    /// 
    /// //用于where子句
    /// db.ParseToSql&lt;string>(name => name.Contains("小明")).ShouldBe("`name` like '%小明%'");
    /// //枚举
    /// db.ParseToSql&lt;EnumFlag>(flag => flag == EnumFlag.B).ShouldBe("`flag` = 2");
    /// db.ParseToSql&lt;EnumFlag>(flag => flag.ContainsAny(EnumFlag.A | EnumFlag.B), prefix: "t", enum2String: true).ShouldBe("(concat_ws(', ',t.`flag`,',') like '%, A,%' or concat_ws(', ',t.`flag`,',') like '%, B,%')");
    /// 
    /// //直接解析json
    /// db.ParseToSql&lt;JsonObject>(ext => ext["name"].ToString() == "name", "t").ShouldBe("convert(json_value(t.`ext`,'$."name"'),char) = 'name'")
    /// db.ParseToSql&lt;Dictionary&lt;string, string>>(ext => ext["name"] == "name").ShouldBe("(json_value(`ext`,'$."name"' returning char)) = 'name'")
    /// </code>
    /// </summary>
    public string ParseToSql<T>(Expression<Func<T, object>> expression, string prefix = null, bool? enum2String = null) => ParseToSqlInternal(expression, [prefix], enum2String == null ? [] : [enum2String.Value]);
    ///<inheritdoc cref="ParseToSql{T}(Expression{Func{T, object}}, string, bool?)"/>
    public string ParseToSql<T, T2>(Expression<Func<T, T2, object>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="ParseToSql{T}(Expression{Func{T, object}}, string, bool?)"/>
    public string ParseToSql<T, T2, T3>(Expression<Func<T, T2, T3, object>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="ParseToSql{T}(Expression{Func{T, object}}, string, bool?)"/>
    public string ParseToSql<T, T2, T3, T4>(Expression<Func<T, T2, T3, T4, object>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="ParseToSql{T}(Expression{Func{T, object}}, string, bool?)"/>
    public string ParseToSql<T, T2, T3, T4, T5>(Expression<Func<T, T2, T3, T4, T5, object>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="ParseToSql{T}(Expression{Func{T, object}}, string, bool?)"/>
    public string ParseToSql<T, T2, T3, T4, T5, T6>(Expression<Func<T, T2, T3, T4, T5, T6, object>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    #endregion

    #region WhereSeg
    /// <summary>
    /// 用于拼接 where 条件, 示例(mysql):
    /// <code>
    /// db.WhereSeg&lt;string>(name => name.Contains("小明")).ShouldBe("where `name` like '%小明%'");
    /// //枚举
    /// db.WhereSeg&lt;EnumFlag>(flag => flag == EnumFlag.B).ShouldBe("where `flag` = 2");
    /// db.WhereSeg&lt;EnumFlag>(flag => flag.ContainsAny(EnumFlag.A | EnumFlag.B), prefix: "t", enum2String: true).ShouldBe("where (concat_ws(', ',t.`flag`,',') like '%, A,%' or concat_ws(', ',t.`flag`,',') like '%, B,%')");
    /// 
    /// //直接解析json
    /// db.WhereSeg&lt;JsonObject>(ext => ext["name"].ToString() == "name", "t").ShouldBe("where convert(json_value(t.`ext`,'$."name"'),char) = 'name'")
    /// db.WhereSeg&lt;Dictionary&lt;string, string>>(ext => ext["name"] == "name").ShouldBe("where (json_value(`ext`,'$."name"' returning char)) = 'name'")
    /// </code>
    /// </summary>
    public string WhereSeg<T>(Expression<Func<T, bool>> expression, string prefix = null, bool? enum2String = null) => $"where {ParseToSqlInternal(expression, [prefix], enum2String == null ? [] : [enum2String.Value])}";
    ///<inheritdoc cref="WhereSeg{T}(Expression{Func{T, bool}}, string, bool?)"/>
    public string WhereSeg<T, T2>(Expression<Func<T, T2, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="WhereSeg{T}(Expression{Func{T, bool}}, string, bool?)"/>
    public string WhereSeg<T, T2, T3>(Expression<Func<T, T2, T3, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="WhereSeg{T}(Expression{Func{T, bool}}, string, bool?)"/>
    public string WhereSeg<T, T2, T3, T4>(Expression<Func<T, T2, T3, T4, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="WhereSeg{T}(Expression{Func{T, bool}}, string, bool?)"/>
    public string WhereSeg<T, T2, T3, T4, T5>(Expression<Func<T, T2, T3, T4, T5, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="WhereSeg{T}(Expression{Func{T, bool}}, string, bool?)"/>
    public string WhereSeg<T, T2, T3, T4, T5, T6>(Expression<Func<T, T2, T3, T4, T5, T6, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => ParseToSqlInternal(expression, prefixs, enum2Strings);
    #endregion

    #region SelectSeg
    /// <summary>
    /// 获取实体类的select子句, 示例:
    /// <code>
    /// //只要参数名以 '_' 开头则省略表别名
    /// db.SelectSeg&lt;Person>(_t => _t.Id).ShouldBe("`id`");
    /// 
    /// //选择全部列
    /// db.SelectSeg&lt;Person>().ShouldBe("`id`,`name`,`age`,`birth`,`props`");
    /// //选择全部列
    /// db.SelectSeg&lt;Person>(t => t).ShouldBe("t.`id`,t.`name`,t.`age`,t.`birth`,t.`props`");
    /// 
    /// //下面 Ext.Score 是json类型 如: [JsonMap]public ExtInfo Ext { get; set; } public class ExtInfo{ public double Score {get;set;} }
    /// db.SelectSeg&lt;Person>(t => new { t.Id, Remark = t.Age > 18 ? "成年" : "未成年", t.Ext.Score }).ShouldBe("t.`id` `Id`,if((t.`age` > 18),'成年','未成年') `Remark`,json_value(t.`Ext`,'$.\"Score\"' returning double) `Score`");
    /// 
    /// //直接解析json
    /// db.SelectSeg&lt;JsonObject>(ext => ext["name"].ToString() == "name", "t").ShouldBe("convert(json_value(t.`ext`,'$."name"'),char) = 'name'")
    /// db.SelectSeg&lt;Dictionary&lt;string, string>>(ext => ext["name"] == "name").ShouldBe("(json_value(`ext`,'$."name"' returning char)) = 'name'")
    /// </code>
    /// </summary>
    public string SelectSeg<T>(Expression<Func<T, object>> expression)
    {
        if (expression == null)
            return GetEntityInfoInternal<T>().Props.Where(i => i.CanMapToTable && !i.IsIgnoreInsert).Select(i => i.SelectFunc(null)).ToStringSeparated(",");
        if (expression.Body == expression.Parameters.First())
        {
            var alias = expression.Parameters.First().Name;
            if (alias.StartsWith('_')) alias = null;
            return GetEntityInfoInternal<T>().Props.Where(i => i.CanMapToTable && !i.IsIgnoreInsert).Select(i => i.SelectFunc(alias)).ToStringSeparated(",");
        }
        return ParseToSqlInternal(expression, null, null);
    }
    #endregion

    #region AndSeg
    private string AndInternal(LambdaExpression expression, string[] prefixs, bool[] enum2Strings)
    {
        var sql = ParseToSqlInternal(expression, prefixs, enum2Strings);
        return $"and {sql}";
    }
    private string AndIfInternal(bool isTrue, LambdaExpression expression, string[] prefixs, bool[] enum2String)
    {
        if (!isTrue) return string.Empty;
        return AndInternal(expression, prefixs, enum2String);
    }

    /// <summary>
    /// and构建器,示例:
    /// <list type="number">
    /// <item>sqlserver: db.AndSeg&lt;string>(name => name.Contains("小明")).ShouldBe("and [name] like '%小明%')</item>
    /// <item>sqlserver: db.AndSeg&lt;string, int>((name, age) => name.Contains("小明") &amp;&amp; age>18, "t").ShouldBe("and t.[name] like '%小明%' and t.[age]>18")</item>
    /// <item>sqlserver: db.AndSeg&lt;string>(true, name => name.Contains("小明")).ShouldBe("and [name] like '%小明%'")</item>
    /// <item>mysql: db.AndSeg&lt;Person>(p => p.Birth >= DateTime.Parse("1990-01-01")).ShouldBe("and p.`birth` > '1990-01-01 00:00:00.000'")</item>
    /// <item>db.AndSeg&lt;JsonArray>(props => props[1]["name"].GetValue&lt;string>() == "小明").ShouldBe(@"and (json_value(json_value(`props`,'$[1]'),'$.""name""' returning char)) = '小明'")</item>
    /// </list>
    /// </summary>
    public string AndSeg<T>(Expression<Func<T, bool>> expression, string prefix = null, bool enum2String = false) => AndInternal(expression, prefix.IsNotNullOrWhiteSpace() ? [prefix] : null, [enum2String]);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSegIf<T>(bool isTrue, Expression<Func<T, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndIfInternal(isTrue, expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSeg<T, T2>(Expression<Func<T, T2, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSegIf<T, T2>(bool isTrue, Expression<Func<T, T2, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndIfInternal(isTrue, expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSeg<T, T2, T3>(Expression<Func<T, T2, T3, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSegIf<T, T2, T3>(bool isTrue, Expression<Func<T, T2, T3, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndIfInternal(isTrue, expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSeg<T, T2, T3, T4>(Expression<Func<T, T2, T3, T4, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSegIf<T, T2, T3, T4>(bool isTrue, Expression<Func<T, T2, T3, T4, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndIfInternal(isTrue, expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSeg<T, T2, T3, T4, T5>(Expression<Func<T, T2, T3, T4, T5, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSegIf<T, T2, T3, T4, T5>(bool isTrue, Expression<Func<T, T2, T3, T4, T5, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndIfInternal(isTrue, expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSeg<T, T2, T3, T4, T5, T6>(Expression<Func<T, T2, T3, T4, T5, T6, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndInternal(expression, prefixs, enum2Strings);
    ///<inheritdoc cref="AndSeg{T}(Expression{Func{T, bool}}, string, bool)"/>
    public string AndSegIf<T, T2, T3, T4, T5, T6>(bool isTrue, Expression<Func<T, T2, T3, T4, T5, T6, bool>> expression, string[] prefixs = null, bool[] enum2Strings = null) => AndIfInternal(isTrue, expression, prefixs, enum2Strings);
    #endregion

    #region CaseSeg
    #region 无实体触发
    /// <summary>
    /// 使用示例:
    /// <code>
    /// //示例1:
    /// db.CaseSeg("id")
    ///    .WhenSeg(0).Then("root")
    ///    .WhenSeg(1).Then("admin")
    ///    .ElseSeg("guest")
    ///    .EndAs("user_name");
    ///  //输出(忽略换行缩进): case id when 0 then 'root' when 1 then 'admin' else 'guest' end user_name
    /// 
    /// //示例2:
    /// db.CaseSeg()
    ///    .WhenSeg(new RawString("t.age>18")).Then("成人")
    ///    .ElseSeg("未成年")
    ///    .End()
    /// //输出(忽略换行缩进): case when t.age>18 then '成人' else '未成年' end
    /// 
    /// //示例3:
    /// db.CaseSeg()
    ///    .WhenSeg&lt;int, EnumSex>((age,sex) => age > 18 &amp;&amp; sex == EnumSex.Male, new string[]{"t",null}, new bool []{false,true}).Then("成年男性")
    ///    .ElseSeg("其他")
    ///    .EndAs("flag")
    /// //输出(忽略换行缩进): case when (t.`age`>18 and `sex` == 'Male') then '成人' else '其他' end flag
    /// 
    /// //示例4:
    /// db.CaseSeg&lt;Person>(t => t.Sex)
    ///     .WhenSeg(EnumSex.Male).Then("男")
    ///     .WhenSeg(EnumSex.FeMale).Then("女")
    ///     .ElseSeg("未知")
    ///     .EndAs("性别")
    ///  //输出(忽略换行缩进): case t.`Sex` when 0 then '男' when 1 then '女' else '未知' end 性别
    /// </code>
    /// </summary>
    public CaseSegBuilder CaseSeg(string colName)
    {
        AssertUtil.NotNullOrWhiteSpace(colName);
        return new CaseSegBuilder(this, colName);
    }
    /// <inheritdoc cref="CaseSeg(string)"/>
    public CaseSegBuilder CaseSeg() => new(this);
    #endregion

    #region 有实体触发
    /// <inheritdoc cref="CaseSeg(string)"/>
    public CaseSegBuilder CaseSeg<T>(Expression<Func<T, object>> expression)
    {
        AssertUtil.NotNull(expression);
        return new CaseSegBuilder(this, expression);
    }
    /// <inheritdoc cref="CaseSeg(string)"/>
    public CaseSegBuilder CaseSeg<T, T2>(Expression<Func<T, T2, object>> expression)
        where T : class, new()
        where T2 : class, new()
    {
        AssertUtil.NotNull(expression);
        return new CaseSegBuilder(this, expression);
    }
    /// <inheritdoc cref="CaseSeg(string)"/>
    public CaseSegBuilder CaseSeg<T, T2, T3>(Expression<Func<T, T2, T3, object>> expression)
        where T : class, new()
        where T2 : class, new()
        where T3 : class, new()
    {
        AssertUtil.NotNull(expression);
        return new CaseSegBuilder(this, expression);
    }
    /// <inheritdoc cref="CaseSeg(string)"/>
    public CaseSegBuilder CaseSeg<T, T2, T3, T4>(Expression<Func<T, T2, T3, T4, object>> expression)
        where T : class, new()
        where T2 : class, new()
        where T3 : class, new()
        where T4 : class, new()
    {
        AssertUtil.NotNull(expression);
        return new CaseSegBuilder(this, expression);
    }
    /// <inheritdoc cref="CaseSeg(string)"/>
    public CaseSegBuilder CaseSeg<T, T2, T3, T4, T5>(Expression<Func<T, T2, T3, T4, T5, object>> expression)
        where T : class, new()
        where T2 : class, new()
        where T3 : class, new()
        where T4 : class, new()
        where T5 : class, new()
    {
        AssertUtil.NotNull(expression);
        return new CaseSegBuilder(this, expression);
    }
    #endregion

    #region CaseList
    /// <summary>
    /// 使用示例(可用于生成update语句):
    /// <code>
    /// var list = new List&lt;PersonEntity>
    ///            { 
    ///                new PersonEntity { Id = 1, Age = 10 },
    ///                new PersonEntity { Id = 2, Age = 20 },
    ///            }
    /// db.CaseListSeg(list, i => i.Id, i => i.Age).End();
    /// //输出(忽略换行缩进): case `id` when 1 then 10 when 2 then 20 end
    /// </code>
    /// </summary>
    public CaseListSegBuilder<T> CaseListSeg<T>(IEnumerable<T> list, Expression<Func<T, object>> whenExp, Expression<Func<T, object>> thenExp) where T : class, new()
    {
        AssertUtil.NotNull(whenExp);
        AssertUtil.NotNull(thenExp);
        return new CaseListSegBuilder<T>(this, list, whenExp, thenExp);
    }
    #endregion
    #endregion

    #endregion

    #region GenerateWhereClause
    /// <summary>
    /// 将传入的 filter 转成 where语句，示例:
    /// <list type="bullet">
    /// <item>传入: "and age>18" 返回: "where age>18"</item>
    /// <item>传入: "or age>18" 返回: "where age>18"</item>
    /// <item>传入: "age>18" 返回: "where age>18"</item>
    /// <item>传入: "where id=1" 返回: "where id=1"</item>
    /// <item>传入: "(age>10) and (score>90)" 返回: "where (age>10) and (score>90)"</item>
    /// <item>传入: "" 返回: "where 1=1"</item>
    /// <item>传入: null 返回: "where 1=1"</item>
    /// </list>
    /// </summary>
    public string GenerateWhereClause(string filter)
    {
        if (filter.IsNullOrWhiteSpace()) return "where 1=1";
        filter = filter.Trim();
        if (StartsWithSeg(filter, "where"))
        {
            //以 where 条件开头
            return filter;
        }
        else if (StartsWithSeg(filter, "and"))
        {
            //以 and 条件开头
            return $"where {filter["and".Length..].TrimStart()}";
        }
        else if (StartsWithSeg(filter, "or"))
        {
            //以 or 条件开头
            return $"where {filter["or".Length..].TrimStart()}";
        }
        else if (StartsWithSeg(filter, "("))
        {
            //以 ( 条件开头
            return $"where {filter}";
        }
        else
        {
            //其他
            return $"where {filter}";
        }
    }
    /// <summary>
    /// 是否以某个seg开头, 如: 
    /// StartsWithSeg("where id>10", "where") => true
    /// 而 StartsWithSeg("whereid>10", "where") => false
    /// </summary>
    private bool StartsWithSeg(string str, string seg)
    {
        return str.StartsWith(seg, StringComparison.OrdinalIgnoreCase) && str[..(seg.Length + 1)].TrimEnd().Equals(seg, StringComparison.OrdinalIgnoreCase);
    }
    #endregion
}
